package com.haiertvbic.tvnow.db;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.haiertvbic.tvnow.model.ChannelLogoBean;
import com.haiertvbic.tvnow.model.ProgramBean;
import com.haiertvbic.tvnow.model.ProgramItemBean;
import com.haiertvbic.tvnow.util.SLog;

public class DbAdapter {
	private final String DB_NAME = "database.db";
	private final int DB_VERSION = 4;

	// 节目字段
	public static final String PROGRAM_PID = "pid";
	public static final String PROGRAM_PWEEKDAY = "pweekday";
	public static final String PROGRAM_PSTIME = "pstime";
	public static final String PROGRAM_PFTIME = "pftime";
	public static final String PROGRAM_PN = "pn";
	public static final String PROGRAM_EPGID = "epgid";
	public static final String PROGRAM_CID = "cid";
	public static final String PROGRAM_CNAME = "cname";
	public static final String PROGRAM_PTYPE = "ptype";
	public static final String PROGRAM_PRECOMMENT = "precomment";
	public static final String PICURL = "picurl";
	public static final String PROGRAM_FID = "fid";
	public static final String PROGRAM_FID2 = "fid2";

	// 分类子段 node,name,pnode
	public static final String ITEM_NODE = "node";
	public static final String ITEM_NAME = "name";
	public static final String ITEM_PNODE = "pnode";
	private static final String TAG = "EPGWeb";

	// 台标子段
	public static final String CNAME = "cname";
	public static final String CID = "cid";
	public static final String LOGORUL = "logorul";
	private DbHelper dbHelper;
	private SQLiteDatabase db;
	private boolean deleteflag = true; // 删数据的开关，默认是删的

	public SQLiteDatabase getDb() {
		return db;
	}

	public void setDb(SQLiteDatabase db) {
		this.db = db;
	}

	private static DbAdapter dbAdapter;

	private DbAdapter(Context ctx) {
		dbHelper = new DbHelper(ctx);
		open();
	}

	public static DbAdapter getInstance(Context ctxt) {
		if (dbAdapter == null) {
			dbAdapter = new DbAdapter(ctxt);
		}
		return dbAdapter;
	}

	public void open() {
		db = dbHelper.getWritableDatabase();
	}

	public void close() {
		SLog.e(TAG, "dbadapter close");
		db.close();
		dbHelper.close();
	}

	/**
	 * 存放节目信息
	 * 
	 * @param program
	 * @return
	 */
	public synchronized boolean addProgram(ArrayList<ProgramBean> programs) {
		boolean result = false;
		db.beginTransaction();
		Object[] bindArgs;
		ProgramBean programBean;
		try {
			for (int i = 0; i < programs.size(); i++) {
				programBean = programs.get(i);
				bindArgs = new Object[] { programBean.getPid(),
						programBean.getPweekday(), programBean.getPstime(),
						programBean.getPftime(), programBean.getPn(),
						programBean.getEpgid(), programBean.getCid(),
						programBean.getCname(), programBean.getPtype(),
						programBean.getPrecommend(), programBean.getPicurl(),
						programBean.getFid(), programBean.getFid2() };

				if (db != null && db.isOpen()) {
					db.execSQL(
							"insert into week_program  "
									+ "(pid,pweekday,pstime,pftime,pn,epgid,cid,cname,ptype,precomment,picurl,fid,fid2)"
									+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?)",
							bindArgs);
					result = true;
				}
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			SLog.i("EPGWeb", "@@@@@@@@@@@@@@" + e.toString());
		} finally {
			db.endTransaction();
			// this.deletePrograms(String.valueOf(week));
		}
		return result;
	}

	public boolean addLogos(ArrayList<ChannelLogoBean> logo_xml) {
		boolean result = false;
		db.beginTransaction();
		Object[] bindArgs;
		ChannelLogoBean logo;
		try {
			for (int i = 0; i < logo_xml.size(); i++) {
				logo = logo_xml.get(i);
				bindArgs = new Object[] { logo.getCid(), logo.getCname(),
						logo.getLogourl() };

				if (db != null && db.isOpen()) {
					db.execSQL("insert into channel_logo  "
							+ "(cid,cname,logourl)" + "values(?,?,?)", bindArgs);
					result = true;
				}
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			SLog.i(TAG, e.toString());
		} finally {
			db.endTransaction();
		}
		return result;
	}

	/**
	 * 根据频道id找节目
	 * 
	 * @param cid频道id
	 * @return
	 */
	public Cursor findProByChannelId(String cid, String week) {
		open();
		Cursor c = null;
		if (db != null && db.isOpen()) {
			String col[] = new String[] { "pid", "pweekday", "pstime",
					"pftime", "pn", "epgid", "cid", "cname", "ptype",
					"precomment", "picurl", "fid", "fid2" };
			c = db.query(
					true,
					"week_program",
					col,
					"cid=? and cast(pftime as datetime) > cast( ? as datetime) and pweekday = ?",
					new String[] {
							cid,
							new SimpleDateFormat("HH:mm:ss").format(new Date()),
							week }, null, null, "pstime", null);
		}
		if (deleteflag) {
			this.deletePrograms(String.valueOf(week));
		}
		SLog.d(TAG, "删除了数据的节目标识为========" + cid);
		return c;
	}

	public Cursor findLogoByChannelId(String channel_id) {
		open();
		Cursor c = null;
		if (db != null && db.isOpen()) {
			String col[] = new String[] { "cid", "cname", "logourl" };
			c = db.query("channel_logo", col, null, null, null, null, null,
					null);
		}
		return c;
	}

	/**
	 * 根据节目编号找节目
	 * 
	 * @param pid
	 *            节目的唯一标志
	 * @return
	 */
	public Cursor findProByChannelPid(String pid, String week) {
		open();
		Cursor c = null;
		if (db != null && db.isOpen()) {
			String col[] = new String[] { "pid", "pweekday", "pstime",
					"pftime", "pn", "epgid", "cid", "cname", "ptype",
					"precomment", "picurl", "fid", "fid2" };
			c = db.query(
					true,
					"week_program",
					col,
					"pid=? and cast(pftime as datetime) > cast( ? as datetime) and pweekday = ?",
					new String[] {
							pid,
							new SimpleDateFormat("HH:mm:ss").format(new Date()),
							week }, null, null, "pstime", null);
		}
		if (deleteflag) {
			this.deletePrograms(String.valueOf(week));
		}
		SLog.d(TAG, "删除了数据的节目标识为========" + pid);
		return c;
	}

	/**
	 * 删除指定周数的节目
	 * 
	 * @param pro_week
	 * @return 若删除成功返回真
	 */
	public boolean deletePrograms(String pro_week) {
		open();
		int num = 0;
		if (db != null && db.isOpen()) {
			// TB_LOCAL_CHANNEL_INFO, LOCAL_C_CID + "=" + cid, null);
			// PROGRAM_PWEEKDAY+"="+pro_week
			num = db.delete(
					"week_program",
					"pweekday < ? or (pweekday = ? and (cast(pftime as datetime) <= cast( ? as datetime)))",
					new String[] { pro_week, pro_week,
							new SimpleDateFormat("HH:mm:ss").format(new Date()) });
		}
		SLog.i(TAG, "删除了数据条数=====" + num);
		return num > 0;
	}

	public Cursor findProByPidAndPro(ProgramBean program, String cid) {
		open();
		Cursor c = null;
		if (db != null && db.isOpen()) {
			String col[] = new String[] { "pid", "pweekday", "pstime",
					"pftime", "pn", "epgid", "cid", "cname", "ptype",
					"precomment", "picurl", "fid", "fid2" };
			if (program != null) {
				c = db.query(
						"week_program",
						col,
						"pid=? and cid=? and pweekday=? and pstime=? and pftime=?",
						new String[] { program.getPid() + "", cid,
								program.getPweekday() + "",
								program.getPstime(), program.getPftime() },
						null, null, null);
			} else {
				c = db.query("week_program", col, "pid=?",
						new String[] { program.getPid() + "" }, null, null,
						null);
			}
		}
		return c;
	}

	public boolean addItem(ProgramItemBean item) {
		// TODO Auto-generated method stub
		boolean result = false;
		Object[] bindArgs = { item.getNode(), item.getName(), item.getPnode() };
		if (db != null && db.isOpen()) {
			db.execSQL("insert into pro_item  " + "(node,name,pnode)"
					+ "values(?,?,?)", bindArgs);
			result = true;
		}
		return result;
	}

	/**
	 * 查找分类的信息，这个存储后一般不变，也不会改
	 * 
	 * @return
	 */
	public Cursor findAllItem() {
		// TODO Auto-generated method stub
		open();
		Cursor c = null;
		if (db != null && db.isOpen()) {
			String col[] = new String[] { "node", "name", "pnode" };
			c = db.query("pro_item", col, null, null, null, null, null);
		}
		return c;
	}

	class DbHelper extends SQLiteOpenHelper {

		private Context context;
		private InitAppDataBase initAppDataBase = new InitAppDataBase();
		private SQLiteDatabase db;

		public DbHelper(Context context) {
			super(context, DB_NAME, null, DB_VERSION);
			this.context = context;
			db = context.openOrCreateDatabase(DB_NAME, 0, null);
			int version = db.getVersion();
			SLog.d("db", "DbHelper version==" + version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			SLog.d("db", "DbHelper onCreate method");

			// 初始化DB
			SLog.d("db", "DbHelper---initAppDataBase----copyDBFile");
			initAppDataBase.copyDBFile(context);
			db = context.openOrCreateDatabase(DB_NAME, 0, null);
			db.setVersion(DB_VERSION);
			SLog.d("db", "DbHelper version==" + db.getVersion());

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			SLog.d("db", "DbHelper---onUpgrade");
			if (db.inTransaction()) {
				initAppDataBase.deleteDbFile(context);
				onCreate(db);
			}

		}

	}

}
